package net.zjitc.jdbc.book;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


/**
 * @Author admin
 * @Date 2019/4/26 8:15
 *
 * 图书馆工具类
 * 完成图书的增删改查
 */
public class BookDaoDb {

    /**
     * 将book对象添加到数据库中，返回影响了几行
     */
    Connection conn = JDBC.getConnction();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    /**
     *   获得数据库连接，
     *   获取操作sql 语句的对象pstmt
     *   执行sql语句
     *   关闭
     * */
    /*添加功能：将book对象保存在Db 要求：图书名称不能重复*/
    public int insert(Book book){

        Book book1 = query(book.getName());
        if (book1==null){
            String sql = "insert into book(name,press,author ,price) values(?,?,?,?)";
            try {
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, book.getName());
                pstmt.setString(2, book.getPress());
                pstmt.setString(3, book.getAuthor());
                pstmt.setDouble(4, book.getPrice());
              int  c = pstmt.executeUpdate();
              return c;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    private Book query(String name) {
        String sql="select *from book where name =?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,name);
            rs=pstmt.executeQuery();
            /*因为书名不能重复，所以结果集中只查询一天语句*/
            if (rs.next()){
                /*结果集中的内容 返回*/
                int id=rs.getInt("id");
                String bookName=rs.getString("name") ;
                String press=rs.getString("press");
                String author=rs.getString("author");
                double price=rs.getDouble("price");
                Book book=new Book(id,name,press,author,price);
                return  book;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     *   查询功能：
     * */
     public void select(){
      String sql="select* from book";
         try {
             pstmt=conn.prepareStatement(sql);
             rs=pstmt.executeQuery();
             while (rs.next()){
                 int id=rs.getInt("id");
                 String name=rs.getString("name");
                 String press=rs.getString("press");
                 String author=rs.getString("author");
                 double price =rs.getDouble("price");
                 System.out.println(id+"\t"+name+"\t"+press+"\t"+author+"\t"+price);
             }
         } catch (SQLException e) {
             e.printStackTrace();
         }


     }
     public ArrayList<Book> selectall(){
         ArrayList<Book> arrayList=new ArrayList<Book>();
         String sql="select* from book";
         try {
             pstmt=conn.prepareStatement(sql);
             rs=pstmt.executeQuery();
             while (rs.next()){
                 int id=rs.getInt("id");
                 String name=rs.getString("name");
                 String press=rs.getString("press");
                 String author=rs.getString("author");
                 double price =rs.getDouble("price");
                 Book book=new Book(id,name,press,author,price);
                 arrayList.add(book);
             }
             return arrayList;
         } catch (SQLException e) {
             e.printStackTrace();
         }
     return null;
     }
}